package com.lq.common;

public class SqlConstant {


    /**
     * 校验测点是否存在
     */
    public static final String SQL_SELECT_CHECK_POINT_MQTT = " select * from measure_point_config_mqtt where 0 = 0 ";


    /**
     * 插入测点数据
     */
    public static final String SQL_INSERT_POINT_MQTT =
            " insert ignore into measure_point_config_mqtt " +
                    " (company_code, area_code, device_id, device_id_inside, device_host_id, measuring_point_addr, measuring_point_name, storehouse_property, save_type, " +
                    " temperature_alarms_upper_limit, temperature_alarms_lower_limit, temperature_warning_upper_limit, temperature_warning_lower_limit," +
                    " humidity_alarms_upper_limit, humidity_alarms_lower_limit, humidity_warning_upper_limit, humidity_warning_lower_limit, date) " +
                    " values ( ?,?,?,?,?,?,?,?,?,  ?,?,?,?,  ?,?,?,?, sysdate() ) ";

    /**
     * 修改测点数据
     */
    public static final String SQL_UPDATE_POINT_MQTT =
            " update measure_point_config_mqtt set company_code=?, area_code=?, device_id=?, device_id_inside=?, device_host_id=?, measuring_point_addr=?, storehouse_property=?, " +
                    " temperature_alarms_upper_limit=?,  temperature_alarms_lower_limit=?, temperature_warning_upper_limit=?, temperature_warning_lower_limit=?," +
                    " humidity_alarms_upper_limit=?,  humidity_alarms_lower_limit=?, humidity_warning_upper_limit=?, humidity_warning_lower_limit=?, date = NOW() where device_id = ? ";


    /**
     * 插入电子围栏主机定位信息
     */
    public static final String SQL_INSER_INTO_FENCE_HOST_GPS = """
            INSERT IGNORE INTO fence_host_gps (
            	host_sn,
            	company_code,
            	area_code,
            	longitude,
            	latitude,
            	location_time,
            	unique_number
            )
            VALUES
            	( ?,?,?,?,?,?,? )
            	on duplicate key update company_code =?, area_code =?, longitude = ?, latitude =?, location_time = ?, unique_number = ?
            """;

    /**
     * 插入设备信息
     */
    public static final String SQL_INSER_INTO_MEASURE_MQTT = """
            INSERT IGNORE INTO measure_point_config_mqtt (
            	company_code,
            	area_code,
            	device_id,
            	device_id_inside,
            	device_host_id,
            	measuring_point_addr,
            	measuring_point_name,
            	storehouse_property,
            	monitoring_type,
            	host_equipment_number,
            	serial_number,
            	save_type,
            	state,
            	x,
            	y,
            	temperature_alarms_upper_limit,
            	temperature_alarms_lower_limit,
            	temperature_warning_upper_limit,
            	temperature_warning_lower_limit,
            	humidity_alarms_upper_limit,
            	humidity_alarms_lower_limit,
            	humidity_warning_upper_limit,
            	humidity_warning_lower_limit,
            	host_id,
            	extend_sn,
            	date 
            )
            VALUES
            	( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, ?,?,?,?, ?,?,?,?,?,?, sysdate() )
            	on duplicate key update company_code = ?,
            	area_code =?,
            	device_id = ?,
            	device_id_inside =?,
            	device_host_id =?,
            	measuring_point_addr =?,
            	measuring_point_name =?,
            	storehouse_property =?,
            	monitoring_type =?,
            	host_equipment_number =?,
            	serial_number =?,
            	save_type =?,
            	state =?,
            	x =?,
            	y =?,
            	temperature_alarms_upper_limit =?,
            	temperature_alarms_lower_limit =?,
            	temperature_warning_upper_limit =?,
            	temperature_warning_lower_limit =?,
            	humidity_alarms_upper_limit =?,
            	humidity_alarms_lower_limit =?,
            	humidity_warning_upper_limit =?,
            	humidity_warning_lower_limit =?,
            	host_id =?,
            	extend_sn =?,
            	date = sysdate()
            """;

    /**
     * 插入校准到期时间
     */
    public static final String SQL_INSER_INTO_MEASURE_CALIBRATION_MQTT = """
                    replace into measure_calibration(company_code, area_code, point_addr, device_sn, host_sn, table_name, expiration_time) values (?,?,?,?,?,?,?)
                    """;

    /**
     * 插入分层信息
     */
    public static final String SQL_INSER_INTO_COMPANY_AREA_FLOOR = """
            INSERT IGNORE INTO company_area_floor (
            	company_code,
            	area_code,
            	storey_id,
            	storey,
            	px,
            	datasource
            )
            VALUES
            	( ?,?,?,?,?,?)
            	on duplicate key update storey = ?, px =?, datasource = ?
            """;

    /**
     * 插入分层图片
     */
    public static final String SQL_INSER_INTO_COMPANY_AREA_FLOOR_IMG= """
            INSERT IGNORE INTO company_area_floor_img (
            	company_code,
            	area_code,
            	storey_id,
            	area_img,
            	px,
            	datasource
            )
            VALUES
            	( ?,?,?,?,?,?)
            	on duplicate key update area_img = ?, px =?, datasource = ?
            """;


    /**
     * 插入分层测点
     */
    public static final String SQL_INSER_INTO_COMPANY_AREA_FLOOR_POINT = """
            INSERT IGNORE INTO company_area_floor_point (
            	company_code,
            	area_code,
            	storey_id,
            	device_unique,
            	point_addr,
            	x,
            	y,
            	isshow,
            	table_name
            )
            VALUES
            	( ?,?,?,?,?,?,?,?,?)
            	on duplicate key update point_addr =?, x =?, y =?, isshow =?, table_name = ?
            """;


    /**
     * 插入measure_point_config_mqtt设备测点
     */
    public static final String SQL_INSER_INTO_MQTT_POINT = """
            INSERT IGNORE INTO measure_point_config_mqtt (
            	company_code,
            	area_code,
            	measuring_point_addr,
            	x,
            	y,
            	isshow
            )
            VALUES
            	( ?,?,?,?,?,?)
            	on duplicate key update x =?, y =?, isshow =?
            """;

    /**
     * 插入measure_point_config_assemble
     */
    public static final String SQL_INSER_INTO_ASSEMBLE_POINT = """
            INSERT IGNORE INTO measure_point_config_assemble (
            	device_unique,
            	x,
            	y,
            	isshow,
            	table_name
            )
            VALUES
            	( ?,?,?,?,?)
            	on duplicate key update x =?, y =?, isshow =?, table_name = ?
            """;

    /**
     * 插入设备唯一标识
     */
    public static final String SQL_REPLACE_INTO_EQUIPMENT_UNIQUE = """
            INSERT IGNORE INTO equipment_unique (
            	unique_number,
            	unique_name,
            	storehouse_property,
            	param_jump,
            	sensor,
            	display_type, is_point_addr,  is_point_name, is_host_number, is_consistent, is_location, binding_type, monitoring_type,
            	tem_alarm_high_limit, tem_alarm_lower_limit, tem_warning_high_limit, tem_warning_lower_limit,
            	hum_alarm_high_limit, hum_alarm_lower_limit, hum_warning_high_limit, hum_warning_lower_limit
            )
            VALUES
            	( ?,?,?,?,?,?,?,?,?,?,?,?,?,  ?,?,?,?,  ?,?,?,? )
            	ON DUPLICATE KEY UPDATE unique_name =?,
            	storehouse_property =?,
            	param_jump =?,
            	sensor=?,
            	display_type =?, is_point_addr =?,  is_point_name =?, is_host_number =?, is_consistent =?, is_location =?, binding_type =?, monitoring_type =?,
            	tem_alarm_high_limit =?, tem_alarm_lower_limit =?, tem_warning_high_limit =?, tem_warning_lower_limit =?,
            	hum_alarm_high_limit =?, hum_alarm_lower_limit =?, hum_warning_high_limit =?, hum_warning_lower_limit =?
            """;


    /**
     * 查询企业信息
     */
    public static final  String SQL_SELECT_COMPANY = """
            select company_code from company where length(company_code) != 0 
            """;


    /**
     * 查询设备数据
     */
    public static final String SQL_SELECT_EQUIPMENT_CONFIG = """
                 SELECT
            	`box`.`device_id` AS `id`,
            	`box`.`company_code` AS `company_code`,
            	`box`.`area_code` AS `area_code`,
            	`box`.`storehouse_property` AS `storehouse_property`,
            	`box`.`date` AS `date`,
            	`box`.`measuring_point_name` AS `measuring_point_name`,
            	`box`.`measuring_point_addr` AS `measuring_point_addr`,
            	`box`.`x` AS `x`,
            	`box`.`y` AS `y`,
            	`box`.`alarm_high_limit` AS `temperature_alarms_upper_limit`,
            	`box`.`alarm_lower_limit` AS `temperature_alarms_lower_limit`,
            	`box`.`warning_high_limit` AS `temperature_warning_upper_limit`,
            	`box`.`warning_lower_limit` AS `temperature_warning_lower_limit`,
            	`box`.`hum_alarm_high_limit` AS `humidity_alarms_upper_limit`,
            	`box`.`hum_alarm_lower_limit` AS `humidity_alarms_lower_limit`,
            	`box`.`hum_warning_high_limit` AS `humidity_warning_upper_limit`,
            	`box`.`hum_warning_lower_limit` AS `humidity_warning_lower_limit`,
            	`box`.`save_type` AS `save_type`,
            	`box`.`is_inuse` AS `is_inuse`,
            	`box`.`monitoring_type` AS `monitoring_type`,
            	`box`.`device_host_id` AS `device_host_id`,
            	`box`.`device_id` AS `device_id`,
            	`box`.`device_id_inside` AS `device_id_inside`,
            	'0' AS `is_add`,
            	'0' AS `push_type`,
            	'cooler_box' AS `table_name`,
            	`box`.`id` AS `realid`,
            	`box`.`state` AS `state`,
            	`box`.`host_id` AS `host_id`  FROM ( `cooler_box` `box`)  WHERE ( `box`.`push_type` <> 0 and  `box`.`device_id` <> '' ) 
            UNION ALL
            	SELECT
            	`pointmqtt`.`id` AS `id`,
            	`pointmqtt`.`company_code` AS `company_code`,
            	`pointmqtt`.`area_code` AS `area_code`,
            	`pointmqtt`.`storehouse_property` AS `storehouse_property`,
            	`pointmqtt`.`date` AS `date`,
            	`pointmqtt`.`measuring_point_name` AS `measuring_point_name`,
            	`pointmqtt`.`measuring_point_addr` AS `measuring_point_addr`,
            	`pointmqtt`.`x` AS `x`,
            	`pointmqtt`.`y` AS `y`,
            	`pointmqtt`.`temperature_alarms_upper_limit` AS `temperature_alarms_upper_limit`,
            	`pointmqtt`.`temperature_alarms_lower_limit` AS `temperature_alarms_lower_limit`,
            	`pointmqtt`.`temperature_warning_upper_limit` AS `temperature_warning_upper_limit`,
            	`pointmqtt`.`temperature_warning_lower_limit` AS `temperature_warning_lower_limit`,
            	`pointmqtt`.`humidity_alarms_upper_limit` AS `humidity_alarms_upper_limit`,
            	`pointmqtt`.`humidity_alarms_lower_limit` AS `humidity_alarms_lower_limit`,
            	`pointmqtt`.`humidity_warning_upper_limit` AS `humidity_warning_upper_limit`,
            	`pointmqtt`.`humidity_warning_lower_limit` AS `humidity_warning_lower_limit`,
            	`pointmqtt`.`save_type` AS `save_type`,
            	`pointmqtt`.`is_inuse` AS `is_inuse`,
            	`pointmqtt`.`monitoring_type` AS `monitoring_type`,
            	`pointmqtt`.`device_host_id` AS `device_host_id`,
            	`pointmqtt`.`device_id` AS `device_id`,
            	`pointmqtt`.`device_id_inside` AS `device_id_inside`,
            	'0' AS `is_add`,
            	'1' AS `push_type`,
            	'measure_point_config_mqtt' AS `table_name`,
            	`pointmqtt`.`id` AS `realid`,
            	`pointmqtt`.`state` AS `state`,
            	`pointmqtt`.`host_id` AS `host_id`  FROM ( `measure_point_config_mqtt` `pointmqtt` )  WHERE ( `pointmqtt`.`company_code` <> '' and `pointmqtt`.`measuring_point_name` <> '')
            """;

    /**
     * 修改数据状态：接口数据处理异常
     */
    public static final String SQL_UPDATE_GPIB_RECORD = """
            UPDATE gpib_record set EXCLUSIVE = ? where id = ?
            """;

    /**
     * 查询异常数据
     */
    public static final String SQL_SELECT_GPIB_RECORD = " select  * from  gpib_record  where  exclusive = ? ";

    /**
     * 插入接口处理异常表
     */
    public static final String SQL_INSERT_GPIB_RECORD =
            " insert ignore into `gpib_record` (company_code, area_code, measuring_point_addr, " +
                    " temperature_alarms_upper_limit, temperature_alarms_lower_limit, temperature_warning_upper_limit, temperature_warning_lower_limit, " +
                    " humidity_alarms_upper_limit, humidity_alarms_lower_limit, humidity_warning_upper_limit, humidity_warning_lower_limit, " +
                    "temperature, humidity, date, gps_longitude, gps_latitude, record_remark, type_number, device_host_id, device_id, data_type, push_type)" +
                    " values ( ?,?,?,  ?,?,?,?, ?,?,?,?,   ?,?,?,?,?,?,?,?,?,?,? ) ";


    /**
     * 插入历史总表数据（mqtt）
     */
    public static final String SQL_INSERT_RECORD =
            " insert ignore into record_mqtt ( company_code, area_code, measuring_point_addr, temperature, humidity, date, gps_longitude, gps_latitude, over_limit_mark, type_number, device_sn_hex_10, device_sn_hex_16, device_host_sn)" +
                    " values ( ?,?,?,?,?,?,?,?,?,?,?,?,? ) ";


    /**
     * 插入超限总表数据（mqtt）
     */
    public static final String SQL_INSERT_RECORD_ALARMS =
            " insert ignore into record_alarms_mqtt  ( company_code, area_code, measuring_point_addr, " +
                    " temperature_alarms_upper_limit, temperature_alarms_lower_limit, temperature_warning_upper_limit, temperature_warning_lower_limit, " +
                    " humidity_alarms_upper_limit, humidity_alarms_lower_limit, humidity_warning_upper_limit, humidity_warning_lower_limit,  " +
                    " temperature, humidity, gps_longitude, gps_latitude, date, record_remark, type_number, device_sn_hex_10, device_sn_hex_16, device_host_sn ) " +
                    " values (?,?,?, ?,?,?,?, ?,?,?,?, ?,?,?,?,?,?,?,?,?,?) ";


    /**
     * 插入GPS定位总表数据（mqtt）
     */
    public static final String SQL_INSERT_GPS =
            " insert ignore into gps_mqtt ( company_code, area_code, measuring_point_addr, " +
                    "  date, gps_longitude, gps_latitude, type_number, device_sn_hex_10, device_host_sn ) " +
                    " values (?,?,?, ?,?,?,?,?,? ) ";


    /**
     * 插入事件总表数据
     */
    public static final String SQL_INSERT_EVENTS =
            " insert ignore into events ( company_code, area_code, measuring_point_addr, temperature, humidity, " +
                    "  date, remark, event_type, over_limit_mark, alarm_value, device_id, device_id_inside, device_host_id, push_type) " +
                    " values (?,?,?, ?,?,?,?,?,?,?,?,?,?,? ) ";

    /**
     * 插入设备任务记录数据
     */
    public static final String SQL_INSERT_RECORD_TASK =
            " insert ignore into record_task ( company_code, area_code, device_host_id, task_num, " +
                    "  dt_start, dt_end, type_number) " +
                    " values (?,?,?,?,?, ?,?) ";


    /**
     * 记录追踪
     */
    public static final String SQL_REPLACE_TARK_PINGTAI = """
            INSERT INTO track_pingtai ( record_id, company_area_code, type )  VALUES  (?, ?, ?) 
            ON DUPLICATE KEY UPDATE record_id = ?, company_area_code = ?, type = ?
            """;

    /**
     * 查询追踪记录节点
     */
    public static final String SQL_SELECT_TARK_PINGTAI = """
            select * from track_pingtai where company_area_code = ?  and type = ?
            """;



    /**
     * 创建数据库表
     */
    public static final String SQL_CREATE_RECORD_MQTT = """
            CREATE TABLE IF NOT EXISTS `record_mqtt` (
              `id` bigint(64) NOT NULL AUTO_INCREMENT COMMENT '主键',
              `company_code` varchar(20) NOT NULL DEFAULT '' COMMENT '企业编码',
              `area_code` varchar(15) NOT NULL DEFAULT '' COMMENT '库区编码',
              `measuring_point_addr` varchar(50) NOT NULL DEFAULT '' COMMENT '测点编号',
              `temperature` float DEFAULT '0' COMMENT '温度值',
              `humidity` float DEFAULT '0' COMMENT '湿度值',
              `date` datetime NOT NULL COMMENT '数据时间',
              `gps_longitude` varchar(12) DEFAULT '' COMMENT 'GPS经度',
              `gps_latitude` varchar(12) DEFAULT '' COMMENT 'GPS纬度',
              `record_remark` varchar(50) DEFAULT '' COMMENT '备注信息',
              `exclusive` varchar(50) DEFAULT NULL COMMENT '处理状态（null或空字符串是需要处理的；uuid是正在处理的；000是处理异常的）',
              `over_limit_mark` varchar(10) DEFAULT NULL COMMENT '超限标识',
              `type_number` varchar(10) DEFAULT NULL COMMENT '类型编码',
              `device_sn_hex_10` varchar(20) DEFAULT NULL COMMENT '设备序列号10进制',
              `device_sn_hex_16` varchar(20) DEFAULT NULL COMMENT '设备序列号16进制',
              `device_host_sn` varchar(20) DEFAULT NULL COMMENT '主机序列号',
              PRIMARY KEY (`id`) USING BTREE,
              UNIQUE KEY `Unique` (`device_sn_hex_10`,`date`) USING BTREE,
              KEY `e` (`exclusive`) USING BTREE,
              KEY `d` (`date`) USING BTREE,
              KEY `ed` (`exclusive`,`date`) USING BTREE
            ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='MQTT历史总表';
            """;

    public static final String SQL_CREATE_RECORD_ALARMS_MQTT = """
            CREATE TABLE IF NOT EXISTS `record_alarms_mqtt` (
              `id` int(32) NOT NULL AUTO_INCREMENT COMMENT '表唯一标识',
              `company_code` varchar(20) NOT NULL DEFAULT '' COMMENT '企业编码',
              `area_code` varchar(15) NOT NULL DEFAULT '' COMMENT '库区编码',
              `measuring_point_addr` varchar(50) NOT NULL DEFAULT '' COMMENT '测点地址',
              `temperature_alarms_upper_limit` float DEFAULT '0' COMMENT '温度报警上限',
              `temperature_alarms_lower_limit` float DEFAULT '0' COMMENT '温度报警下限',
              `temperature_warning_upper_limit` float DEFAULT '0' COMMENT '温度预警上限',
              `temperature_warning_lower_limit` float DEFAULT '0' COMMENT '温度预警下限',
              `humidity_alarms_upper_limit` float DEFAULT '0' COMMENT '湿度报警上限',
              `humidity_alarms_lower_limit` float DEFAULT '0' COMMENT '湿度报警下限',
              `humidity_warning_upper_limit` float DEFAULT '0' COMMENT '湿度预警上限',
              `humidity_warning_lower_limit` float DEFAULT '0' COMMENT '湿度预警下限',
              `temperature` float DEFAULT '0' COMMENT '温度值',
              `humidity` float DEFAULT '0' COMMENT '湿度值',
              `date` datetime DEFAULT NULL COMMENT '数据时间',
              `record_remark` varchar(200) DEFAULT NULL COMMENT '备注信息',
              `gps_longitude` varchar(12) DEFAULT '' COMMENT 'GPS经度',
              `gps_latitude` varchar(12) DEFAULT '' COMMENT 'GPS纬度',
              `exclusive` varchar(50) DEFAULT NULL COMMENT '处理状态（null或空字符串是需要处理的；uuid是正在处理的；000是处理异常的）',
              `over_limit_mark` varchar(10) DEFAULT NULL COMMENT '超限标识',
              `type_number` varchar(10) DEFAULT NULL COMMENT '类型编码',
              `device_sn_hex_10` varchar(20) DEFAULT NULL COMMENT '设备序列号10进制',
              `device_sn_hex_16` varchar(20) DEFAULT NULL COMMENT '设备序列号16进制',
              `device_host_sn` varchar(20) DEFAULT NULL COMMENT '主机序列号',
              PRIMARY KEY (`id`) USING BTREE,
              UNIQUE KEY `Unique` (`device_sn_hex_10`,`date`) USING BTREE,
              KEY `e` (`exclusive`) USING BTREE,
              KEY `d` (`date`) USING BTREE,
              KEY `ed` (`date`,`exclusive`) USING BTREE
            ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='MQTT超限总表';
            """;

    public static final String SQL_CREATE_GPS_MQTT = """
            CREATE TABLE IF NOT EXISTS `gps_mqtt` (
              `id` bigint(64) NOT NULL AUTO_INCREMENT COMMENT '主键',
              `company_code` varchar(20) NOT NULL DEFAULT '' COMMENT '企业编码',
              `area_code` varchar(15) NOT NULL DEFAULT '' COMMENT '库区编码',
              `measuring_point_addr` varchar(50) NOT NULL DEFAULT '' COMMENT '测点编号',
              `date` datetime NOT NULL COMMENT '数据时间',
              `gps_longitude` varchar(12) DEFAULT '' COMMENT 'GPS经度',
              `gps_latitude` varchar(12) DEFAULT '' COMMENT 'GPS纬度',
              `exclusive` varchar(50) DEFAULT NULL COMMENT '处理状态（null或空字符串是需要处理的；uuid是正在处理的；000是处理异常的）',
              `type_number` varchar(10) DEFAULT NULL COMMENT '类型编码',
              `device_sn_hex_10` varchar(20) DEFAULT NULL COMMENT '设备序列号10进制',
              `device_sn_hex_16` varchar(20) DEFAULT NULL COMMENT '设备序列号16进制',
              `device_host_sn` varchar(20) DEFAULT NULL COMMENT '主机序列号',
              PRIMARY KEY (`id`) USING BTREE,
              UNIQUE KEY `Unique` (`device_sn_hex_10`,`date`) USING BTREE,
              KEY `e` (`exclusive`) USING BTREE,
              KEY `d` (`date`) USING BTREE,
              KEY `ed` (`exclusive`,`date`) USING BTREE
            ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='MQTT定位总表';
            """;
}
